﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmProspectsMangFollowUp

    Dim FolderPath As String = FTPAddress + "Prospects Cases"

    Sub FillProspects()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Cond As String = ""
            If Me.RExpireIn75Days.Checked = True Then
                Cond = "and DateDiff(d,getdate(),ExpiryDate)<=75"
            ElseIf Me.RShowAll.Checked = True Then
                Cond = ""
            End If
            Dim Status As String
            If RadReady.Checked = True Then
                Status = "and Status=N'Ready'"
            ElseIf RadUnderProcess.Checked = True Then
                Status = "and Status=N'Under Process'"
            ElseIf RadRecived.Checked = True Then
                Status = "and Status=N'Received'"
            ElseIf RadActive.Checked = True Then
                Status = "and Status=N'Active'"
            ElseIf RadConfirmed.Checked = True Then
                Status = "and Status=N'Firm Order'"
            ElseIf RadWon.Checked = True Then
                Status = "and Status=N'Won'"
            ElseIf RadAll.Checked = True Then
                Status = ""
            End If

            'Check Filter
            If Me.RFilterCompanyName.Checked = True Then
                Cond += " and InsuranceCompany Like N'%" & Me.txtCompanyName.Text.Trim & "%'"
            End If

            Dim cmd As New SqlCommand("Select SNo,PNo,ProsNo,ProsType,Status,ProspectName,InsuranceCompany,InsuranceBroker,ClassofBusiness,BusinessSize" & _
                                      ",ExpiryDate,IsNull(AssignedTo,N'') AssignedTo," & _
                                      "IsNull(AssignedBy,N'') AssignedBy,AssignedDate,SavedUser," & _
                                      "TransDate,IsNull(dbo.GetProspectLastUpdate(SNo),N'') LastUpdate,SentToUR From " & _
                                      "ProspectCases Where Branch ='" & Branch & "'" & Status & " " & Cond, cnn)
            Dim Reader As SqlDataReader
            Dim AssignDate, ExpiryDate, LastUpdate As String

            Me.GridProspects.Rows.Clear()
            Me.txtCompanyName.AutoCompleteCustomSource.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                If Reader.Item("AssignedDate").ToString = "1/1/1900 12:00:00 AM" Then
                    AssignDate = ""
                Else
                    AssignDate = CDate(Reader.Item("AssignedDate")).ToString("yyyy/MM/dd")
                End If

                If Reader.Item("ExpiryDate") Is DBNull.Value Then
                    ExpiryDate = ""
                Else
                    ExpiryDate = CDate(Reader.Item("ExpiryDate")).ToString("yyyy/MM/dd")
                End If

                If Reader.Item("LastUpdate").ToString = "1/1/1900 12:00:00 AM" Then
                    LastUpdate = ""
                Else
                    LastUpdate = CDate(Reader.Item("LastUpdate")).ToString("yyyy/MM/dd")
                End If

                Me.GridProspects.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("ProsNo"), Reader.Item("Status"),
                                                        ExpiryDate, Reader.Item("ProspectName"), _
                                                        Reader.Item("InsuranceCompany"), Reader.Item("InsuranceBroker"), _
                                                        Reader.Item("ClassofBusiness"), Reader.Item("BusinessSize"), _
                                                        Reader.Item("AssignedTo"), Reader.Item("AssignedBy"), AssignDate, _
                                                        Reader.Item("SavedUser"), Format(CDate(Reader.Item("TransDate")), "yyyy/MM/dd"), _
                                                        LastUpdate, Reader.Item("SentToUR"), "Send To U/R"})

                Me.txtCompanyName.AutoCompleteCustomSource.Add(Reader.Item("InsuranceCompany"))
            End While
            cnn.Close()

            'Check Colors
            For Each row As DataGridViewRow In Me.GridProspects.Rows
                If row.Cells(Me.DGColStatus.Index).Value = "Active" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Yellow
                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Ready" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Green
                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Under Process" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Fuchsia
                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Lost" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Red
                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Firm Order" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Purple
                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Received" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Khaki
                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Won" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.GreenYellow
                End If

            Next

            Me.LabelResult.Text = "Count: " & Me.GridProspects.Rows.Count

            'Check Expiry Date
            'For Each Row As DataGridViewRow In Me.GridProspects.Rows
            '    If Row.Cells(Me.DGColExpiry.Index).Value = "" Then

            '    Else
            '        If Now.Date >= CType(Row.Cells(Me.DGColExpiry.Index).Value, Date).Date Then
            '            Row.Cells(Me.DGColExpiry.Index).Style.BackColor = Color.Red
            '        Else
            '            Row.Cells(Me.DGColExpiry.Index).Style.BackColor = Color.White
            '        End If
            '    End If
            'Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmProspectsFollowUp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.LabelResult.Text = "Count: " & Me.GridProspects.Rows.Count
        Me.WindowState = FormWindowState.Maximized

    End Sub

    Private Sub GridProspects_CurrentCellChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GridProspects.CurrentCellChanged
        Try
            Me.Cursor = Cursors.WaitCursor

            If Me.GridProspects.CurrentRow Is Nothing Then
                Me.Cursor = Cursors.Default
            Else
                Me.txtProspectNo.Text = Me.GridProspects.CurrentRow.Cells(1).Value
                FillProspectsNotes()
                FillProspectsDocuments()
            End If

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillProspectsNotes()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From ProspectsNotes Where ProsNo=" & Me.GridProspects.CurrentRow.Cells(0).Value & "Order By SNo", cnn1)
            Dim Reader As SqlDataReader

            Me.txtProgressNotes.Clear()

            Dim ListHeader, ListNotes As New ArrayList

            cnn1.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.txtProgressNotes.Text += Reader.Item("SavedUser") & "     " & Format(CDate(Reader.Item("SavedDate")), "yyyy/MM/dd hh:mm:ss tt") & _
                                        Chr(13) & Reader.Item("Notes") & ".    " & Reader.Item("Type") & Chr(13) & Chr(13)

                ListHeader.Add(Reader.Item("SavedUser") & "     " & Format(CDate(Reader.Item("SavedDate")), "yyyy/MM/dd hh:mm:ss tt"))
                ListNotes.Add(Reader.Item("Notes"))
            End While
            cnn1.Close()

            For Each T As String In ListHeader
                txtProgressNotes.SelectionStart = txtProgressNotes.Find(T)
                Dim bfont As New Font(txtProgressNotes.Font, FontStyle.Bold)
                txtProgressNotes.SelectionFont = bfont
                txtProgressNotes.SelectionColor = Color.Blue
            Next

            Me.txtProgressNotes.ScrollToCaret()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillProspectsDocuments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Dir As New IO.DirectoryInfo(FolderPath & "\" & Me.GridProspects.CurrentRow.Cells(1).Value) 'sub is prospect no!
            Dim DirFiles As IO.FileInfo() = Dir.GetFiles()

            Me.GridFiles.Rows.Clear()

            'list the names of all files in the specified directory
            For Each Fi As FileInfo In DirFiles
                Me.GridFiles.Rows.Add(New String() {Fi.Name, Fi.CreationTime.ToString("yyyy/MM/dd hh:mm tt"), "Open", "Delete"})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        SaveNotes()
    End Sub

    Sub SaveNotes()
        If Me.txtNotes.Text.Trim.Length = 0 Then
            MsgBox("Please enter your message")
            Me.txtNotes.Clear()
            Me.txtNotes.Focus()
            Exit Sub
        Else
            Try
                Dim Type As String
                If RadPhoneCall.Checked = True Then
                    Type = "Phone Call"
                ElseIf RadVisit.Checked = True Then
                    Type = "Visit"
                ElseIf RadOther.Checked = True Then
                    Type = "Other"
                End If

                Me.Cursor = Cursors.WaitCursor
                Dim cmd As New SqlCommand("Insert Into ProspectsNotes (ProsNo,Notes,Type,SavedUser) Values " & _
                                          "(@ProsNo,@Notes,@Type,@SavedUser)", cnn)
                cnn.Open()

                'Add values
                cmd.Parameters.AddWithValue("@ProsNo", Me.GridProspects.CurrentRow.Cells(0).Value)
                cmd.Parameters.AddWithValue("@Notes", Me.txtNotes.Text.Trim)
                cmd.Parameters.AddWithValue("@Type", Type)
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                cmd.ExecuteNonQuery()
                cnn.Close()

                FillProspectsNotes()

                Me.txtNotes.Clear()
                Me.txtNotes.Focus()

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub GridFiles_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = 2 Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim FilePath As String = FolderPath + "\" + Me.GridProspects.CurrentRow.Cells(1).Value + "\" + Me.GridFiles.Rows(e.RowIndex).Cells(0).Value
                System.Diagnostics.Process.Start(FilePath)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub RExpireIn75Days_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RExpireIn75Days.CheckedChanged
        CheckRadio()
        FillProspects()
    End Sub

    Private Sub RShowAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RShowAll.CheckedChanged
        CheckRadio()
        FillProspects()
    End Sub

    Sub CheckStatus()
        If Me.RFilterCompanyName.Checked = True Then
            Me.txtCompanyName.Enabled = True
            Me.txtCompanyName.Focus()

        ElseIf Me.RFilterAll.Checked = True Then
            Me.txtCompanyName.Enabled = False
        End If
    End Sub

    Private Sub RFilterAll_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RFilterAll.CheckedChanged
        CheckStatus()
        FillProspects()
    End Sub

    Private Sub RFilterCompanyName_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RFilterCompanyName.CheckedChanged
        CheckStatus()
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        FillProspects()
    End Sub

    Private Sub txtCompanyName_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtCompanyName.KeyUp
        If e.KeyCode = Keys.Enter Then
            FillProspects()
        End If
    End Sub

    Private Sub GridProspects_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProspects.CellDoubleClick
        
        Dim a As New frmProspectsCasesStatus
        a.SNo = CInt(Me.GridProspects.CurrentRow.Cells(0).Value)
        a.ShowDialog()
        FillProspects()

    End Sub

    Private Sub GridProspects_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridProspects.CellClick
        If e.ColumnIndex = Me.DGColSendUR.Index Then

            Dim cmd As New SqlCommand("Update ProspectCases set Status=@Status,SentToUR=1,SendUser=N'" & CurrentUser & "',SendDate=getdate() " & _
                                      "where SNo=" & Me.GridProspects.CurrentRow.Cells(0).Value, cnn)
            cnn.Open()

            'Add values
            cmd.Parameters.AddWithValue("@Status", "Under Process")
            cmd.Parameters.AddWithValue("@SendUser", CurrentUser)
            cmd.ExecuteNonQuery()
            cnn.Close()

            FillProspects()
        End If
    End Sub

    Sub CheckRadio()
        If Me.RadReady.Checked = True Then
            Me.GridProspects.Columns(Me.DGColSendUR.Index).Visible = True
        Else
            Me.GridProspects.Columns(Me.DGColSendUR.Index).Visible = False
        End If
    End Sub

    Private Sub RadReady_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadReady.CheckedChanged
        CheckRadio()
    End Sub

    Private Sub RadRecived_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadRecived.CheckedChanged
        FillProspects()
    End Sub

    Private Sub RadUnderProcess_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadUnderProcess.CheckedChanged
        FillProspects()
    End Sub

    Private Sub RadActive_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadAll.CheckedChanged
        FillProspects()
    End Sub

    Private Sub RadActive_CheckedChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadActive.CheckedChanged
        FillProspects()
    End Sub

    Private Sub RadConfirmed_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RadConfirmed.CheckedChanged
        FillProspects()
    End Sub

    Private Sub RadWon_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RadWon.CheckedChanged
        FillProspects()
    End Sub
End Class